﻿USE {DATABASE_NAME}
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}BlobStatusTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].{CONTEXT_PREFIX}BlobStatusTable(
	[Id] [uniqueidentifier] NOT NULL,
	[PropertyName] [nvarchar]({SIZE_PROPERTIES}) NOT NULL,
	[ValueType] [nvarchar](1) NOT NULL,
	[FullyQualifiedType] [nvarchar](512) NOT NULL,
	[Value] [varbinary](MAX) NULL,
 CONSTRAINT [{CONTEXT_PREFIX}PK_EntitiesStatus] PRIMARY KEY CLUSTERED 
(
	[Id] ASC,
	[PropertyName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}AddBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}AddBlobStatusTable] 
    @Id [uniqueidentifier], 
    @PropertyName [nvarchar]({SIZE_PROPERTIES}),
    @ValueType [nvarchar](1),
	@FullyQualifiedType [nvarchar](512),
	@Value [varbinary](MAX) 
    
AS  
    SET NOCOUNT ON
	INSERT INTO [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
           ([Id]
           ,[PropertyName]
           ,[ValueType]
		   ,[FullyQualifiedType] 
           ,[Value])
     VALUES
           (@Id
           ,@PropertyName
           ,@ValueType
		   ,@FullyQualifiedType
           ,@Value)'
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}UpdateBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}UpdateBlobStatusTable] 
    @Id [uniqueidentifier], 
    @PropertyName [nvarchar]({SIZE_PROPERTIES}),
    @ValueType [nvarchar](1),
	@FullyQualifiedType [nvarchar](512),
	@Value [varbinary] (MAX)
    
AS  

    SET NOCOUNT ON
	Update [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
    Set    
           [ValueType]=@ValueType
		   ,[FullyQualifiedType] =@FullyQualifiedType
           ,[Value]=@Value
    where
			Id=@Id and PropertyName=@PropertyName'
END  
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}SelectValueBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].{CONTEXT_PREFIX}SelectValueBlobStatusTable 
	@Id [uniqueidentifier], 
    @PropertyName [nvarchar]({SIZE_PROPERTIES})
   
AS
    SET NOCOUNT ON
	SELECT Top 1 ValueType,Value
	FROM [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
	WHERE  Id=@Id and PropertyName=@PropertyName'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}SelectItemsOfTypeBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].{CONTEXT_PREFIX}SelectItemsOfTypeBlobStatusTable 	
	@FullyQualifiedType [nvarchar](512) 
AS
    SET NOCOUNT ON
	SELECT Value
	FROM [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
	WHERE FullyQualifiedType=@FullyQualifiedType
	UNION
	SELECT Value
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE FullyQualifiedType=@FullyQualifiedType'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}HasValueBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].{CONTEXT_PREFIX}HasValueBlobStatusTable 
	@Id [uniqueidentifier], 
    @PropertyName [nvarchar]({SIZE_PROPERTIES})
   
AS
    SET NOCOUNT ON
	SELECT Count(Id)
	FROM [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
	WHERE  Id=@Id and PropertyName=@PropertyName'
END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}DeleteEntityBlobStatusTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].{CONTEXT_PREFIX}DeleteEntityBlobStatusTable 
	@Id [uniqueidentifier]
AS
    SET NOCOUNT ON
	DELETE  
	FROM [dbo].[{CONTEXT_PREFIX}BlobStatusTable]
	WHERE  Id=@Id' 
END
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{CONTEXT_PREFIX}BlobPartsListTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].{CONTEXT_PREFIX}BlobPartsListTable(
	[Id] [uniqueidentifier] NOT NULL,
	[Position] [int] NOT NULL,
	[Value] [varbinary](MAX) NULL,     
	[FullyQualifiedType] [nvarchar](MAX) NULL,
 CONSTRAINT [{CONTEXT_PREFIX}PK_BlobPartsListTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC,
	[Position] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}AddBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}AddBlobPartsListTable]
    @Id [uniqueidentifier], 
	@Position [int],
	@Value [varbinary](MAX),
	@FullyQualifiedType [nvarchar] (MAX)
    
AS  

    SET NOCOUNT ON
	
	Update [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	Set    
		[Position]=[Position]+1
	WHERE  Id=@Id and [Position] >= @Position
	
	
	INSERT INTO [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
           ([Id]
           ,[Position]
           ,[Value]
           ,[FullyQualifiedType]
           )
     VALUES
           (@Id
           ,@Position
           ,@Value
           ,@FullyQualifiedType)'
END   
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}SelectBlobPartsListTableEntity') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}SelectBlobPartsListTableEntity]
	@Id [uniqueidentifier]
	,@Position [int]
AS
    SET NOCOUNT ON
	SELECT  [FullyQualifiedType],[Value]
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id and [Position]=@Position'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}SelectBlobPartsListTableEntityByEntityId') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}SelectBlobPartsListTableEntityByEntityId]
	@Id [uniqueidentifier],
	@EntityId [varbinary](MAX)
AS
    SET NOCOUNT ON
	SELECT  [Position],[FullyQualifiedType],[Value] 
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id and [Value]=@EntityId'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}SelectBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}SelectBlobPartsListTable]
	@Id [uniqueidentifier]
	
AS
    SET NOCOUNT ON
	SELECT  [Position], [FullyQualifiedType],[Value]
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id' 
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}DeleteBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}DeleteBlobPartsListTable] 
	@Id [uniqueidentifier]
AS
    SET NOCOUNT ON
	DELETE  
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}DeleteBlobPartsListTableEntity') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}DeleteBlobPartsListTableEntity] 
	@Id [uniqueidentifier]
	,@Position [int]
AS
    SET NOCOUNT ON
    
	DELETE  
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id and Position=@Position
	
	UPDATE [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	SET    
		[Position]=[Position]-1
	WHERE  Id=@Id and [Position] > @Position'
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}UpdateBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}UpdateBlobPartsListTable] 
    @Id [uniqueidentifier], 
	@Position [int],
	@Value [varbinary] (MAX),
	@FullyQualifiedType [nvarchar] (MAX)
    
AS  

    SET NOCOUNT ON
	Update [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
    Set    
           [Value]=@Value,
           [FullyQualifiedType]=@FullyQualifiedType 
	WHERE  Id=@Id and Position=@Position'  
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}CopyRangeBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}CopyRangeBlobPartsListTable] 
    @SourceCollectionId [uniqueidentifier], 
    @DestinationCollectionId [uniqueidentifier], 
	@InitialPosition [int],
	@NumItems [int]
    
AS  

    SET NOCOUNT ON
	INSERT INTO [dbo].[{CONTEXT_PREFIX}BlobPartsListTable] ([Id],[Position],[Value],[FullyQualifiedType])
		SELECT @DestinationCollectionId,[Position]-@InitialPosition,[Value],[FullyQualifiedType] 
		FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable] 
		WHERE [Id]=@SourceCollectionId AND [Position]>=@InitialPosition AND [Position]<@InitialPosition+@NumItems'
END		
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}CountBlobPartsListTable') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[{CONTEXT_PREFIX}CountBlobPartsListTable]
	@Id [uniqueidentifier]
AS
    SET NOCOUNT ON
	SELECT Count(1)
	FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable]
	WHERE  Id=@Id' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{CONTEXT_PREFIX}BlobPartReferenced') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE dbo.{CONTEXT_PREFIX}BlobPartReferenced
      @EntityIdBin [varbinary](MAX) 
AS
      SET NOCOUNT ON;

      DECLARE @result int
      
      SET @result =  0
      IF EXISTS (SELECT 1 FROM [dbo].[{CONTEXT_PREFIX}BlobPartsListTable] WHERE Value  = @EntityIdBin)
            SET @result =  1  
      IF EXISTS (SELECT 1 FROM [dbo].[{CONTEXT_PREFIX}BlobStatusTable] WHERE Value  = @EntityIdBin)
            SET @result =  1  

      SELECT @result
      RETURN'
END    
GO

